﻿USE [ParieurProDB]
GO

/****** Object:  Index [IX_Contests_ChampionShipSeasons]    Script Date: 11/29/2011 18:59:27 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_Contests_ChampionShipSeasons] ON [dbo].[Contests] 
(
	[ChampionshipId] ASC,
	[SeasonId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

USE [ParieurProDB]
GO

/****** Object:  Index [IX_EventId_UserId]    Script Date: 12/01/2011 22:34:11 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_EventId_UserId] ON [dbo].[EventScores] 
(
	[UserId] ASC,
	[EventId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


USE [ParieurProDB]
GO
/****** Object:  StoredProcedure [dbo].[mybet_joinContest]    Script Date: 12/11/2011 14:33:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Franck Allion
-- Create date: 26/02/11
-- Description:	User join a contest
-- Output :
--  Erreurs fonctionnelles
--	-8 : Utilisateur déjà inscrit
--  -7 : Utilisateur non autorisé sur le tournoi
--  Erreurs SQL
--  -2 : Erreur à l'inscription
--  -3 : Erreur à la création de l'entrée dans les scores
--  -4 : Erreur à la suppression des rôles
--  -5 : Le rôle "Player" n'existe pas
--  -6 : Erreur lors de l'affectation du rôle "Player"
-- =============================================
ALTER PROCEDURE [dbo].[mybet_joinContest]
	@UserId             uniqueidentifier,
	@CustomContestId    int
AS
BEGIN

    DECLARE @TranStarted   bit
    SET @TranStarted = 0
   
    DECLARE @ErrorCode   int
    SET @ErrorCode = 0
    
    DECLARE @RoleId uniqueidentifier
    SELECT @RoleId = NULL
    
    DECLARE @DefaultContestId int
    SET @DefaultContestId = 0
    
    DECLARE @CurrentContestId int
    SET @CurrentContestId = 0
    
    IF( @@TRANCOUNT = 0 )
		BEGIN
		    BEGIN TRANSACTION
			SET @TranStarted = 1
		END
    
    -- Vérification que l'utilisateur n'est pas déjà inscrit
    SELECT @CurrentContestId=COUNT(Subscriptions.CustomContestId) FROM Subscriptions WHERE
    UserId=@UserId;
    IF ( @CurrentContestId = 1 )
    BEGIN
		-- Déjà inscrit
		SET @ErrorCode = -8
		GOTO Cleanup
    END
    
	-- Verification que l'utilisateur peut rejoindre le tournoi
	-- Récupération du tournoi par défaut
	SELECT @DefaultContestId=CustomContestId FROM CustomContest
		WHERE IsDefault=1;
	IF (@DefaultContestId=@CustomContestId)
	BEGIN
		-- Tournoi par défaut tout le monde a le droit
		GOTO Subscription
	END
		
	-- On vérifie que l'utilisateur est habilité ou qu'il souscrit
	-- au tournoi par défaut
	SELECT @CurrentContestId=COUNT(CustomContest.CustomContestId) FROM CustomContest
	LEFT JOIN UserMailCustomContest UMCC ON UMCC.CustomContestId=CustomContest.CustomContestId
	LEFT JOIN aspnet_Membership members ON members.UserId=@UserId
	WHERE
		 UMCC.LoweredUserEmail=members.LoweredEmail AND CustomContest.CustomContestId=@CustomContestId;
	
	IF ( @CurrentContestId = 0 )
	BEGIN
		-- User not allowed
		SET @ErrorCode = -7
		GOTO Cleanup
	END
	
Subscription:
		
    -- Insert new subscription
    DELETE Subscriptions 
		WHERE UserId=@UserId AND CustomContestId=@CustomContestId;
    
	INSERT Subscriptions (UserId, CustomContestId)
	VALUES (@UserId,@CustomContestId);
	
	IF( @@ERROR <> 0 )
    BEGIN
		SET @ErrorCode = -2
		GOTO Cleanup
    END
    
    INSERT Ranks (EventCount, GoodPredictionCount, GoodScoreCount,
    MatchCount, SubscriptionId, TotalScore, CurrentRank)
    VALUES (0,0,0,0,@@IDENTITY,0,0); 
    
  	IF( @@ERROR <> 0 )
    BEGIN
		SET @ErrorCode = -3
		GOTO Cleanup
    END  
    
    -- Delete user role
	DELETE aspnet_UsersInRoles 
		WHERE UserId=@UserId
	IF( @@ERROR <> 0 )
    BEGIN
		SET @ErrorCode = -4
		GOTO Cleanup
    END		

	-- Give player role
	-- Get Player role id
    SELECT  @RoleId = RoleId FROM dbo.aspnet_Roles WHERE RoleName = 'Player'
    IF ( @RoleId IS NULL )
    BEGIN
 		SET @ErrorCode = -5
		GOTO Cleanup       
    END

    -- Add role	
	INSERT INTO aspnet_UsersInRoles  
	( UserId, 
	  RoleId )
	VALUES
	( @UserId, @RoleId)
	
	IF( @@ERROR <> 0 )
    BEGIN
		SET @ErrorCode = -6
		GOTO Cleanup
    END
	
    -- commit transaction
    IF( @TranStarted = 1 )
    BEGIN
	    SET @TranStarted = 0
	    COMMIT TRANSACTION
    END
    
    SET @ErrorCode = 0
    
    SET ROWCOUNT 1
    
    RETURN 0
    
Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END
    
    SET ROWCOUNT 0
    
	RETURN @ErrorCode    
    
END

GO



USE [ParieurProDB]
GO
/****** Object:  StoredProcedure [dbo].[mybet_disjoinContest]    Script Date: 12/11/2011 18:00:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Franck Allion
-- Create date: 26/02/11
-- Description:	User disjoin a contest
-- =============================================
ALTER PROCEDURE [dbo].[mybet_disjoinContest]
	@UserId             uniqueidentifier,
	@CustomContestId    int
AS
BEGIN

    DECLARE @TranStarted   bit
    SET @TranStarted = 0
   
    DECLARE @ErrorCode   int
    SET @ErrorCode = 0
    
    DECLARE @RoleId uniqueidentifier
    SELECT @RoleId = NULL
    
    DECLARE @SubscriptionId int
    SELECT @SubscriptionId = 0
    
    IF( @@TRANCOUNT = 0 )
		BEGIN
		    BEGIN TRANSACTION
			SET @TranStarted = 1
		END
    
    SELECT  @SubscriptionId = SubscriptionId FROM Subscriptions 
		WHERE UserId=@UserId AND CustomContestId=@CustomContestId
    
   
    IF ( @SubscriptionId = 0 )
    BEGIN
		-- Pas abonné
		SET @ErrorCode = -7
		GOTO Cleanup
    END
 	
 	-- Suppression de l'abonnement
	DELETE Ranks WHERE SubscriptionId=@SubscriptionId    		
 	DELETE Subscriptions WHERE SubscriptionId=@SubscriptionId
    
	DELETE UserBets WHERE UserId=@UserId
	IF( @@ERROR <> 0 )
    BEGIN
		SET @ErrorCode = -3
		GOTO Cleanup
    END
	
	-- Delete user role
	DELETE aspnet_UsersInRoles 
		WHERE UserId=@UserId
	IF( @@ERROR <> 0 )
    BEGIN
		SET @ErrorCode = -4
		GOTO Cleanup
    END		
    
	-- Give player role
	-- Get Player role id
    SELECT  @RoleId = RoleId FROM dbo.aspnet_Roles WHERE RoleName = 'Registered Users'
    IF ( @RoleId IS NULL )
    BEGIN
 		SET @ErrorCode = -5
		GOTO Cleanup       
    END
	
	-- Add role	
	INSERT INTO aspnet_UsersInRoles  
	( UserId, 
	  RoleId )
	VALUES
	( @UserId, @RoleId)
	
	IF( @@ERROR <> 0 )
    BEGIN
		SET @ErrorCode = -6
		GOTO Cleanup
    END
    
    -- commit transaction
    IF( @TranStarted = 1 )
    BEGIN
	    SET @TranStarted = 0
	    COMMIT TRANSACTION
    END
    
    SET @ErrorCode = 0
    
    SET ROWCOUNT 1
    
    RETURN 0
    
Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END
    
    SET ROWCOUNT 0
    
	RETURN @ErrorCode    
    
END

GO

USE [ParieurProDB]
GO

/****** Object:  Index [IX_UserIdUnique]    Script Date: 12/11/2011 18:22:51 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Subscriptions]') AND name = N'IX_UserIdUnique')
DROP INDEX [IX_UserIdUnique] ON [dbo].[Subscriptions] WITH ( ONLINE = OFF )
GO

USE [ParieurProDB]
GO

/****** Object:  Index [IX_UserIdUnique]    Script Date: 12/11/2011 18:22:51 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_UserIdUnique] ON [dbo].[Subscriptions] 
(
	[UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


-- Mémorisation de la date du paris
ALTER TABLE [ParieurProDB].[dbo].[EventScores]
ADD [LastBetUpdate] datetime NULL;
GO

